/**
 * Created by liumengjun on 2017-07-21.
 */
import groovy.sql.Sql

import java.util.logging.Level
import java.util.logging.LogManager
import java.util.logging.Logger

// log config
LogManager.getLogManager().readConfiguration(new FileInputStream(new File("../logging.properties")))

def url = 'jdbc:hsqldb:mem:yourDB'
def user = 'sa'
def password = ''
def driver = 'org.hsqldb.jdbcDriver'
def sql = Sql.newInstance(url, user, password, driver)

//Sql.withInstance(url, user, password, driver) { sql ->
//    // use 'sql' instance ...
//}

// use 'sql' instance ...

// ... create 'sql' instance
sql.execute '''
  CREATE TABLE Author (
    id          INTEGER GENERATED BY DEFAULT AS IDENTITY,
    firstname   VARCHAR(64),
    lastname    VARCHAR(64)
  );
'''
// close 'sql' instance ...

// CRUD
// insert
sql.execute "INSERT INTO Author (firstname, lastname) VALUES ('Dierk', 'Koenig')"

def insertSql = 'INSERT INTO Author (firstname, lastname) VALUES (?,?)'
def params = ['Jon', 'Skeet']
def keys = sql.executeInsert insertSql, params
assert keys[0] == [1]

def first = 'Guillaume'
def last = 'Laforge'
def myKeyNames = ['ID']
def myKeys = sql.executeInsert """
  INSERT INTO Author (firstname, lastname)
  VALUES (${first}, ${last})
""", myKeyNames
assert myKeys[0] == [ID: 2]

// read
sql.query('SELECT firstname, lastname FROM Author') { resultSet ->
    while (resultSet.next()) {
        first = resultSet.getString(1)
        last = resultSet.getString('lastname')
        println "$first $last"
    }
}

sql.eachRow('SELECT firstname, lastname FROM Author') { row ->
    first = row[0]
    last = row.lastname
    println "$first $last"
}

def first_row = sql.firstRow('SELECT lastname, firstname FROM Author')
println first_row
println first_row.values()

List authors = sql.rows('SELECT firstname, lastname FROM Author')
println authors

// count(*)
assert sql.firstRow('SELECT COUNT(*) AS num FROM Author').num == 3

// update
sql.execute "INSERT INTO Author (lastname) VALUES ('Thorvaldsson')"
sql.execute "UPDATE Author SET firstname='Erik' where lastname='Thorvaldsson'"

def updateSql = "UPDATE Author SET lastname='Pragt' where lastname='Thorvaldsson'"
def updateCount = sql.executeUpdate updateSql
assert updateCount == 1

def row = sql.firstRow "SELECT * FROM Author where firstname = 'Erik'"
assert "${row.firstname} ${row.lastname}" == 'Erik Pragt'

// delete
println sql.firstRow('SELECT COUNT(*) AS num FROM Author').num
sql.execute "DELETE FROM Author WHERE lastname = 'Skeet'"
println sql.firstRow('SELECT COUNT(*) AS num FROM Author').num
sql.execute "DELETE FROM Author"
assert sql.firstRow('SELECT COUNT(*) AS num FROM Author').num == 0

/*
 transaction
 */
sql.withTransaction {
    sql.execute "INSERT INTO Author (firstname, lastname) VALUES ('Dierk', 'Koenig')"
    sql.execute "INSERT INTO Author (firstname, lastname) VALUES ('Jon', 'Skeet')"
}
assert sql.firstRow('SELECT COUNT(*) as num FROM Author').num == 2

// fail transaction
def maxFirstnameLength
def metaClosure = { meta -> maxFirstnameLength = meta.getPrecision(1) }
def rowClosure = {}
def rowCountBefore = sql.firstRow('SELECT COUNT(*) as num FROM Author').num
try {
    sql.withTransaction {
        sql.execute "INSERT INTO Author (firstname) VALUES ('Dierk')"
        sql.eachRow "SELECT firstname FROM Author WHERE firstname = 'Dierk'", metaClosure, rowClosure
        sql.execute "INSERT INTO Author (firstname) VALUES (?)", 'X' * (maxFirstnameLength + 1)
    }
} catch (ignore) {
    println ignore.message
}
def rowCountAfter = sql.firstRow('SELECT COUNT(*) as num FROM Author').num
assert rowCountBefore == rowCountAfter

// next line will add fine logging
Logger.getLogger('groovy.sql').level = Level.FINE

// batch
sql.withBatch(3) { stmt ->
    stmt.addBatch "INSERT INTO Author (firstname, lastname) VALUES ('Dierk', 'Koenig')"
    stmt.addBatch "INSERT INTO Author (firstname, lastname) VALUES ('Paul', 'King')"
    stmt.addBatch "INSERT INTO Author (firstname, lastname) VALUES ('Guillaume', 'Laforge')"
    stmt.addBatch "INSERT INTO Author (firstname, lastname) VALUES ('Hamlet', 'D''Arcy')"
    stmt.addBatch "INSERT INTO Author (firstname, lastname) VALUES ('Cedric', 'Champeau')"
    stmt.addBatch "INSERT INTO Author (firstname, lastname) VALUES ('Erik', 'Pragt')"
    stmt.addBatch "INSERT INTO Author (firstname, lastname) VALUES ('Jon', 'Skeet')"
}

def inserts = 'INSERT INTO Author (firstname, lastname) VALUES (?,?)'
sql.withBatch(3, inserts) { ps ->
    ps.addBatch('Dierk', 'Koenig')
    ps.addBatch('Paul', 'King')
    ps.addBatch('Guillaume', 'Laforge')
    ps.addBatch('Hamlet', "D'Arcy")
    ps.addBatch('Cedric', 'Champeau')
    ps.addBatch('Erik', 'Pragt')
    ps.addBatch('Jon', 'Skeet')
}

// set log level info
Logger.getLogger('groovy.sql').level = Level.INFO

// pagination
def qry = 'SELECT * FROM Author'
println sql.rows(qry)*.firstname
println sql.rows(qry, 1, 3)*.firstname
println sql.rows(qry, 5, 3)*.firstname

// Named parameters
sql.execute "INSERT INTO Author (firstname, lastname) VALUES (:first, :last)", first: 'Dierk', last: 'Koenig'
sql.execute "INSERT INTO Author (firstname, lastname) VALUES (?.first, ?.last)", first: 'Jon', last: 'Skeet'

sql.close()
